package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;

import person.Employee;
import person.Person;
import person.Employee.Role;
import contract.EmployeeContract;
import controllers.ExceptionsTypes;

public class EmployeeWrapper {
	
	public static ArrayList<Employee> getAllEmployees(boolean closeConnection) throws Exception{
		ArrayList<Employee> employees = new ArrayList<Employee>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".employee ";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	GregorianCalendar birthDate = new GregorianCalendar();
	        	birthDate.setTimeInMillis(rs.getLong("birth_date"));
	        	String role = rs.getString("role");
        		Role r = Role.valueOf(role);
        		long id = rs.getInt("registration_number");
        		double salary = rs.getDouble("salary");
        		String company = rs.getString("company_referent");
        		Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
        		employees.add(new Employee(id, pers, r, company, birthDate, salary));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    return employees;
	}
	
	public static Employee getEmployee(long idEmployee,boolean closeConnection) throws Exception{
		Employee empl = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".employee " +
				"WHERE registration_number = "+ idEmployee;
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
        	if(rs.next()){
        		GregorianCalendar birthDate = new GregorianCalendar();
        		birthDate.setTimeInMillis(rs.getLong("birth_date"));
        		String role = rs.getString("role");
        		Role r = Role.valueOf(role);
        		double salary = rs.getDouble("salary");
        		String company = rs.getString("company_referent");
        		Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
    			empl = new Employee(idEmployee, pers, r, company, birthDate, salary);
        	}
        	stmt.close();
        	if(closeConnection)
        		DBWrapper.getConnection().close();
	    
	    if(empl == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return empl;
	}
	public static Employee getEmployee(String taxCode, boolean closeConnection) throws Exception{
		Employee empl = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".employee " +
				"WHERE tax_code = '"+ taxCode+"'";
	    
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
        	if(rs.next()){
        		GregorianCalendar birthDate = new GregorianCalendar();
        		birthDate.setTimeInMillis(rs.getLong("birth_date"));
        		String role = rs.getString("role");
        		Role r = Role.valueOf(role);
        		long id = rs.getInt("registration_number");
        		double salary = rs.getDouble("salary");
        		String company = rs.getString("company_referent");
        		Person pers = PersonWrapper.getPerson(taxCode , false);
    			empl = new Employee(id, pers, r, company, birthDate, salary);
        	}
        	stmt.close();
        	if(closeConnection)
        		DBWrapper.getConnection().close();
	    
	    if(empl == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return empl;
	}
	public static ArrayList<Employee> getEmployeeByRole(Role role, boolean closeConnection) throws Exception{
		ArrayList<Employee> employees = new ArrayList<Employee>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".employee " +
	    		"WHERE role = '"+ role+"'";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	GregorianCalendar birthDate = new GregorianCalendar();
	        	birthDate.setTimeInMillis(rs.getLong("birth_date"));
	        	long id = rs.getInt("registration_number");
        		double salary = rs.getDouble("salary");
        		String company = rs.getString("company_referent");
        		Person p = PersonWrapper.getPerson(rs.getString("tax_code"), false);
        		employees.add(new Employee(id, p, role, company, birthDate, salary));	
    			}
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    return employees;
	}
	public static void insertEmployee(boolean closeConnection, Employee ... employee) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<employee.length; i++){
			Employee e = employee[i];
			try{
				PersonWrapper.getPerson(e.getTaxCode(), false);
			}catch(Exception ex){
				if(ex.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
					PersonWrapper.insertPerson(false, e);
				else
					throw ex;
			}
	        int rows = stmt.executeUpdate(
	        	"INSERT INTO "+ DBWrapper.getDbname() +".employee" +
	        	"(tax_code, company_referent, role, salary, birth_date)"+
	            "VALUES('" + 
	            e.getTaxCode() +"', '"+ 
	            e.getReferenceCompany() +"', '" + 
	            e.getRole() + "', "+ 
	            e.getSalary() + ", "+ 
	            e.getBirthDate().getTimeInMillis()+")"); //TODO la data non vera!
	        if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        e.setId(getEmployee(e.getTaxCode(), false).getId());
		}
		
        stmt.close();
        if(closeConnection) 
        	DBWrapper.getConnection().close();
	}	
	public static void deleteEmployee(boolean closeConnection, EmployeeContract employeeCont) throws Exception{
	   
		EmployeeContractWrapper.deleteEmployeeContract(employeeCont, false);
		String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".employee " +
	    		"WHERE tax_code = '"+employeeCont.getPerson().getTaxCode()+"'";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        int rows = stmt.executeUpdate(query);
	        if(rows>1)
	        	throw new SQLException();
	        else if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        PersonWrapper.deletePerson(false, employeeCont.getPerson()); 
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	}
}
